package autoorder.zsxmlv.util;

import autoorder.zsxmlv.po.Account;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: ZSXM
 * @Description:
 * @Company: QIDI
 * @Date: Create by 2018/7/16 20:30
 * @Version: 1.0
 * @Modify by:
 */
public class ExcelUtil {

    public static List<Account> getExcelData(String filePath) {
        File file = new File(filePath);
        List<Account> list = new ArrayList<>();
        Workbook wb = null;
        try {
            InputStream inputStream = new FileInputStream(file);
            String fileName = file.getName();
            if (fileName.endsWith("xls")) {
                wb = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith("xlsx")) {
                wb = new XSSFWorkbook(inputStream);
            }
            Sheet sheet = wb.getSheetAt(0);
            int firstRowNum = sheet.getFirstRowNum() + 1;// 避免重复读取首行
            int lastRowNum = sheet.getLastRowNum() + 1;// 获取最后一行的下标
            // 循环读取每一行数据
            for (int rowIndex = firstRowNum; rowIndex < lastRowNum; rowIndex++) {
                Account account = new Account();
                int index = 0;
                // 获取当前行数据对象
                Row row = sheet.getRow(rowIndex);
                short firstCellNum = row.getFirstCellNum();// 得到第一列
                short lastCellNum = row.getLastCellNum();// 得到最后一列
                for (int cIndex = firstCellNum; cIndex < lastCellNum; cIndex++) {
                    Cell cell = row.getCell(cIndex);
                    if (cell != null) {
                        if(index == 0){
                            index++;
                            DecimalFormat df = new DecimalFormat("0");
                            String str = df.format(cell.getNumericCellValue());
                            account.setUsername(str);
                        }else if(index == 1){
                            index++;
                            account.setCustname(cell.toString());
                        }else if(index == 2){
                            index++;
                            DecimalFormat df = new DecimalFormat("0");
                            String str = df.format(cell.getNumericCellValue());
                            account.setCustpwd(str);
                        }
                    } else {
                        index++;
                        continue;
                    }
                }
                account.setDisabled(0);
                list.add(account);// 添加一行数据
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


}
